package com.salesmonitoring.accounts.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.HashSet;

import com.salesmonitoring.connector.Connector;

public class AccountOfficerDaoImpl {

	private static AccountOfficerDaoImpl accountOfficerDaoImpl;
	
	private Connector connector;
	private Connection connection;
	private Statement statement;
	private ResultSet rs;
	private int rsInt;
	private String query;
	
	public static synchronized AccountOfficerDaoImpl getInstance() {
		if(accountOfficerDaoImpl == null) 
			accountOfficerDaoImpl = new AccountOfficerDaoImpl();
		
		return accountOfficerDaoImpl;
	}
	
	public void insertUser(String name, String email, Integer contact) {
		
		connector = Connector.getInstance();
		query = "INSERT INTO user(user_name, user_email, user_contact) VALUES('"+name+"', '"+email+"', '"+contact+"')";
		
		try {
			connection = connector.getConnection();
			statement = connection.createStatement();
			rsInt = statement.executeUpdate(query);
			
		} catch(Exception e) {
			e.printStackTrace();
			
		} finally {
			
			if(statement != null) {
				try { statement.close(); } catch(Exception e) { e.printStackTrace(); }
			}
			
			if(connection != null) {
				try { connection.close(); } catch(Exception e) { e.printStackTrace(); }
			}
			
		}
		
	}
	
	public String[] getAllAONames() {
		
		ArrayList<String> aos = new ArrayList<String>();
		String[] names = null;
		String query2 = null;
		Statement statement2 = null;
		ResultSet rs2 = null;
		
		aos.add("");
		
		try {
			connector = Connector.getInstance();
			connection = connector.getConnection();
			statement = connection.createStatement();
			statement2 = connection.createStatement();
			
			query = "SELECT user_name FROM user WHERE user_role='account officer'";
			query2 = "SELECT DISTINCT ao_id FROM transaction";
			rs = statement.executeQuery(query);
			rs2 = statement2.executeQuery(query2);
			
			while(rs.next()) {
				aos.add(rs.getString("user_name"));
			}
			
			while(rs2.next()) {
				aos.add(rs2.getString("ao_id"));
			}
			
			HashSet set = new HashSet(aos);
			aos.clear();
			aos.addAll(set);
			
			Collections.sort(aos);
			
			names = aos.toArray(new String[aos.size()]);
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			
			if(rs != null) {
				try { rs.close(); } catch(Exception e) { e.printStackTrace(); }
			}
			
			if(statement != null) {
				try { statement.close(); } catch(Exception e) { e.printStackTrace(); }
			}
			
			if(connection != null) {
				try { connection.close(); } catch(Exception e) { e.printStackTrace(); }
			}
			
		}
		
		return names;
		
	}
	
}
